import pandas as pd
from pathlib import Path
from datetime import datetime

# 配置常量
DATA_DIR = Path("data")
OUTPUT_DIR = DATA_DIR / "alldata"  # 输出目录

# 输入文件
TRAVEL_DETAILS_FILE = DATA_DIR / "差旅报销行程明细(商旅).xlsx"
TRAVEL_ALLOWANCE_FILE = DATA_DIR / "差旅费出差补助.xlsx"
ADMIN_CODE_FILE = DATA_DIR / "序号19-2023年中华人民共和国县以上行政区划代码.xlsx"
ALLOWANCE_STANDARD_FILE = DATA_DIR / "目的地补助标准.xlsx"

# 输出文件
OUTPUT_FILE = OUTPUT_DIR / "超标准报销伙食补助分析结果.xlsx"

def clean_column_names(df):
    """
    清理DataFrame的列名，去除多余的空格
    """
    return df.rename(columns=lambda x: x.strip())

def load_data():
    """
    加载Excel文件的数据并进行基础处理
    
    返回值：
        tuple: 包含DataFrame的元组
    """
    # 确保输出目录存在
    OUTPUT_DIR.mkdir(parents=True, exist_ok=True)
    
    try:
        # 读取Excel文件
        travel_details = pd.read_excel(TRAVEL_DETAILS_FILE, dtype='object')
        travel_allowance = pd.read_excel(TRAVEL_ALLOWANCE_FILE)  # 添加读取差旅费出差补助表
        admin_code = pd.read_excel(ADMIN_CODE_FILE, skiprows=1)  # 跳过第一行，使用第二行作为列名
        allowance_standard = pd.read_excel(ALLOWANCE_STANDARD_FILE)  # 读取目的地补助标准
        
        # 清理列名中的空格
        travel_details = clean_column_names(travel_details)
        travel_allowance = clean_column_names(travel_allowance)
        admin_code = clean_column_names(admin_code)
        allowance_standard = clean_column_names(allowance_standard)
        
        # 转换补助标准表的日期列
        allowance_standard['标准开始日期'] = pd.to_datetime(allowance_standard['标准开始日期'])
        allowance_standard['标准结束日期'] = pd.to_datetime(allowance_standard['标准结束日期'])
        
        # 打印列名，帮助调试
        print("\n第一个表的列名：")
        print(travel_details.columns.tolist())
        print("\n第二个表的列名：")
        print(travel_allowance.columns.tolist())
        print("\n行政区划代码表的列名：")
        print(admin_code.columns.tolist())
        print("\n补助标准表的列名：")
        print(allowance_standard.columns.tolist())
        
        return travel_details, travel_allowance, admin_code, allowance_standard
    
    except Exception as e:
        print(f"加载数据时出错: {str(e)}")
        raise

def match_destination_province(travel_details, admin_code):
    """
    匹配目的地名称对应的省份
    
    Args:
        travel_details: DataFrame 差旅明细数据
        admin_code: DataFrame 行政区划代码数据
    
    Returns:
        DataFrame: 添加了省份信息的差旅明细数据
    """
    try:
        result_df = travel_details.copy()
        
        # 确保行政区划代码为字符串类型
        admin_code['行政区划代码'] = admin_code['行政区划代码'].astype(str)
        
        # 创建省份映射字典
        province_mapping = {}
        # 首先创建城市到行政区划代码的映射
        city_to_code = {}
        for _, row in admin_code.iterrows():
            city_name = str(row['城市名称']) if pd.notna(row['城市名称']) else ''
            code = str(row['行政区划代码']) if pd.notna(row['行政区划代码']) else ''
            if city_name and code:
                city_to_code[city_name] = code
        
        # 创建省级代码到省份名称的映射
        province_code_mapping = {}
        for _, row in admin_code.iterrows():
            code = str(row['行政区划代码']) if pd.notna(row['行政区划代码']) else ''
            city_name = str(row['城市名称']) if pd.notna(row['城市名称']) else ''
            if code.endswith('0000'):  # 省级代码
                province_code_mapping[code] = city_name
        
        # 对每个城市，找到其对应的省份
        for city, code in city_to_code.items():
            if len(code) >= 2:
                province_code = code[:2] + '0000'  # 取前两位，后面补0
                if province_code in province_code_mapping:
                    province_mapping[city] = province_code_mapping[province_code]
        
        # 添加省份列
        result_df['目的地省份'] = result_df['目的地名称'].map(province_mapping)
        
        return result_df
    
    except Exception as e:
        print(f"匹配目的地省份时出错: {str(e)}")
        raise

def get_allowance_standard(province, create_date, allowance_standard_df):
    """
    根据省份和创建日期获取补助标准
    
    Args:
        province: str 省份名称
        create_date: datetime 创建日期
        allowance_standard_df: DataFrame 补助标准数据
    
    Returns:
        tuple: (补助标准金额, 标准开始日期, 标准结束日期)
    """
    try:
        # 如果省份为空或者不是字符串类型，直接使用其他地区标准
        if pd.isna(province) or not isinstance(province, str):
            # 获取其他地区标准
            other_standards = allowance_standard_df[allowance_standard_df['省份'] == '其他地区']
            for _, standard in other_standards.iterrows():
                if standard['标准开始日期'] <= create_date <= standard['标准结束日期']:
                    return (standard['每天补助标准'], 
                           standard['标准开始日期'], 
                           standard['标准结束日期'])
            return (None, None, None)
            
        # 先找到所有可能匹配的省份标准
        for _, standard in allowance_standard_df.iterrows():
            # 跳过其他地区的标准，稍后处理
            if standard['省份'] == '其他地区':
                continue
                
            standard_provinces = [p.strip() for p in str(standard['省份']).split('、')]
            # 检查是否有包含关系（双向）
            for std_province in standard_provinces:
                if std_province in province or province in std_province:
                    # 检查日期范围
                    if standard['标准开始日期'] <= create_date <= standard['标准结束日期']:
                        return (standard['每天补助标准'], 
                               standard['标准开始日期'], 
                               standard['标准结束日期'])
        
        # 如果没有匹配到具体省份的标准，检查其他地区的标准
        other_standards = allowance_standard_df[allowance_standard_df['省份'] == '其他地区']
        for _, standard in other_standards.iterrows():
            if standard['标准开始日期'] <= create_date <= standard['标准结束日期']:
                return (standard['每天补助标准'], 
                       standard['标准开始日期'], 
                       standard['标准结束日期'])
            
        return (None, None, None)
        
    except Exception as e:
        print(f"获取补助标准时出错: {str(e)}")
        return (None, None, None)

def analyze_travel_data(travel_details, travel_allowance, admin_code, allowance_standard):
    """
    分析差旅数据
    """
    try:
        # 首先筛选伙食补助的数据
        result_df = travel_allowance[travel_allowance['补助类型名称'] == '伙食补助'].copy()
        
        print(f"\n筛选出记录数: {len(result_df)}")
        
        # 从差旅明细表获取目的地信息
        destination_info = pd.merge(
            travel_details[['单据编号', '人员名称', '目的地名称']],
            result_df[['单据编号', '人员名称']],
            on=['单据编号', '人员名称'],
            how='right'
        )
        
        # 匹配目的地省份
        destination_info = match_destination_province(destination_info, admin_code)
        
        # 将目的地信息合并回原始数据
        result_df = pd.merge(
            result_df,
            destination_info[['单据编号', '人员名称', '目的地名称', '目的地省份']],
            on=['单据编号', '人员名称'],
            how='left'
        )
        
        # 计算每天补助金额
        result_df['每天补助金额'] = result_df.apply(
            lambda x: x['金额'] / x['天数'] if pd.notna(x['天数']) and x['天数'] != 0 else None,
            axis=1
        )
        
        # 确保创建时间为日期类型
        result_df['创建时间'] = pd.to_datetime(result_df['创建时间'])
        
        # 预处理补助标准表的日期
        allowance_standard['标准开始日期'] = pd.to_datetime(allowance_standard['标准开始日期'])
        allowance_standard['标准结束日期'] = pd.to_datetime(allowance_standard['标准结束日期'])
        
        # 获取每天补助标准和标准时间范围
        result_df[['每天补助标准', '标准开始日期', '标准结束日期']] = result_df.apply(
            lambda x: pd.Series(get_allowance_standard(
                x['目的地省份'],
                x['创建时间'],
                allowance_standard
            )),
            axis=1
        )
        
        # 打印标准匹配统计
        total_records = len(result_df)
        matched_standards = len(result_df[pd.notna(result_df['每天补助标准'])])
        empty_province_records = len(result_df[pd.isna(result_df['目的地省份'])])
        empty_province_matched = len(result_df[
            (pd.isna(result_df['目的地省份'])) & 
            (pd.notna(result_df['每天补助标准']))
        ])
        
        print(f"\n标准匹配统计:")
        print(f"总记录数: {total_records}")
        print(f"成功匹配标准的记录数: {matched_standards}")
        print(f"省份为空的记录数: {empty_province_records}")
        print(f"省份为空但匹配到标准的记录数: {empty_province_matched}")
        
        # 计算每天超标金额
        result_df['每天超标金额'] = result_df.apply(
            lambda x: x['每天补助金额'] - x['每天补助标准']
            if pd.notna(x['每天补助金额']) and pd.notna(x['每天补助标准'])
            else None,
            axis=1
        )
        
        # 创建特殊省份列表
        special_provinces = ['西藏', '青海', '新疆']
        
        # 根据省份和超标金额筛选数据
        filtered_df = pd.DataFrame()  # 创建空的DataFrame用于存储筛选结果
        
        for _, row in result_df.iterrows():
            province = str(row['目的地省份']) if pd.notna(row['目的地省份']) else ''
            daily_excess = row['每天超标金额'] if pd.notna(row['每天超标金额']) else 0
            
            # 检查是否是特殊省份
            is_special = any(p in province for p in special_provinces) if province else False
            
            # 根据不同标准筛选
            if is_special and daily_excess > 120:
                filtered_df = pd.concat([filtered_df, pd.DataFrame([row])], ignore_index=True)
            elif daily_excess > 100:  # 对于其他省份和省份为空的情况
                filtered_df = pd.concat([filtered_df, pd.DataFrame([row])], ignore_index=True)
        
        # 将日期格式化为年月日
        filtered_df['创建时间'] = filtered_df['创建时间'].dt.strftime('%Y-%m-%d')
        filtered_df['标准开始日期'] = filtered_df['标准开始日期'].dt.strftime('%Y-%m-%d')
        filtered_df['标准结束日期'] = filtered_df['标准结束日期'].dt.strftime('%Y-%m-%d')
        
        # 处理所有其他日期列，只保留年月日
        date_columns = filtered_df.select_dtypes(include=['datetime64']).columns
        for col in date_columns:
            filtered_df[col] = filtered_df[col].dt.strftime('%Y-%m-%d')
        
        # 删除重复值
        filtered_df = filtered_df.drop_duplicates()
        
        # 打印超标记录统计信息
        total_excess_records = len(filtered_df)
        special_province_records = len(filtered_df[filtered_df['目的地省份'].apply(
            lambda x: any(p in str(x) for p in special_provinces) if pd.notna(x) else False
        )])
        empty_province_excess = len(filtered_df[pd.isna(filtered_df['目的地省份'])])
        other_province_records = total_excess_records - special_province_records - empty_province_excess
        
        print(f"\n超标记录统计:")
        print(f"总超标记录数: {total_excess_records}")
        print(f"特殊省份超标记录数: {special_province_records}")
        print(f"其他省份超标记录数: {other_province_records}")
        print(f"省份为空的超标记录数: {empty_province_excess}")
        
        return filtered_df
        
    except Exception as e:
        print(f"分析数据时出错: {str(e)}")
        raise

def main(once = False):
    """主程序入口"""
    try:
        print("正在加载数据...")
        travel_details, travel_allowance, admin_code, allowance_standard = load_data()
        
        if any(df.empty for df in [travel_details, travel_allowance, admin_code, allowance_standard]):
            print("错误：输入数据为空")
            return False
            
        print("正在分析数据...")
        result = analyze_travel_data(travel_details, travel_allowance, admin_code, allowance_standard)
        
        if result.empty:
            print("未找到匹配的数据")
            return False

        if once:
            return result

        # 保存结果
        with pd.ExcelWriter(OUTPUT_FILE, engine='openpyxl', mode='w') as writer:
            result.to_excel(writer, index=False)
        print(f"\n分析完成。结果已保存至: {OUTPUT_FILE}")
        print(f"记录总数: {len(result)}")
        
        return True
        
    except Exception as e:
        print(f"处理过程中出现错误: {str(e)}")
        raise

if __name__ == "__main__":
    main() 